package beans;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.Locale;

public class MarkerSearchBean {

	private static final DecimalFormat F = new DecimalFormat("00.000",new DecimalFormatSymbols(Locale.US));
	public String search(String bbox) {
		
		StringBuffer sb = new StringBuffer();
		
		String[] coord = bbox.split(",");
		String maxlat = coord[0];
		String maxlon = coord[1];
		String minlat = coord[2];
		String minlon = coord[3];
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		    Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/nstweb?user=root&password=root");
		    Statement s = c.createStatement();	
			
		    
		    String points = "'MULTIPOINT("+minlon+" "+minlat+","+maxlon+" "+minlat+","+maxlon+" "+maxlat+","+minlon+" "+maxlat+")'";		    
		    String query = "SELECT * FROM  overlay WHERE (Intersects(bbox,GeomFromText("+points+"))) ORDER BY RAND() LIMIT 30;";
		    System.out.println(query);
		
		    
		    ResultSet rs = s.executeQuery(query);
		    int tot = 0;
		    while(rs.next() && tot < 30) {
		    	String city = rs.getString("city");
		    	String state = rs.getString("state");
		    	String activity = rs.getString("activity");
		    	
		    	double north = rs.getDouble("north");
		    	double east = rs.getDouble("east");
		    	double south = rs.getDouble("south");
		    	double west = rs.getDouble("west");
		    	
		    	double lat = (north + south)/2;
		    	double lon = (east + west)/2;
		    	
		    	sb.append(","+F.format(lat)+","+F.format(lon)+","+city+","+state+","+activity);
		    	tot++;
		    }
		    
		    
			s.close();
			c.close();
			
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		if(sb.length() > 2)
			return sb.toString().substring(1);
		return "";
	}
	
	public static void main(String[] args) {
		MarkerSearchBean msb = new MarkerSearchBean();
		String markers = msb.search("45.621722,12.634277,44.545952,10.696564");
		System.out.println(markers);
	}
}
